#!/usr/bin/env python3
import sys
import os
import sqlite3
import datetime
import inspect

sys.path = [r'D:\vann\dev\python'] + sys.path
from py3eg import Console

# /////////////////////////////////////////////////////////////////////////////
class LogWrappedFunction(object):
    __slots__ = ("function",)
    def __init__(self, function):
        self.function = function

    def logAndCall(self, *arguments, **namedArguments):
        print ("Calling {0} with arguments {1} and named arguments {2}".format(self.function.__name__, arguments, namedArguments))
        self.function.__call__(*arguments, **namedArguments)

# -----------------------------------------------------------------------------
def logfunction(function):
    return LogWrappedFunction(function).logAndCall

# /////////////////////////////////////////////////////////////////////////////
class MyDbTest():

    __slots__ = ("_db","DISPLAY_LIMIT")
    DISPLAY_LIMIT = 20
    # -------------------------------------------------------------------------
    def connect(self, filename):
        """
        Connect to database file. Create table is the database was created.
        """
        create = not os.path.exists(filename)
        self._db = sqlite3.connect(filename)
        if create:
            cursor = self._db.cursor()
            cursor.execute( "CREATE TABLE directors ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
                            "name TEXT UNIQUE NOT NULL)")
            cursor.execute( "CREATE TABLE dvds ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
                            "title TEXT NOT NULL, "
                            "year INTEGER NOT NULL, "
                            "duration INTEGER NOT NULL, "
                            "director_id INTEGER NOT NULL, "
                            "FOREIGN KEY (director_id) REFERENCES directors)")
            self._db.commit()

    # -------------------------------------------------------------------------
    def add_dvd(self):
        title = Console.get_string("Title")
        if not title:
            return
        director = Console.get_string("Director")
        if not director:
            return
        year = Console.get_integer("Year", "year", minimum=1896, maximum=datetime.date.today().year)
        duration = Console.get_string("Duration")
        director_id = self.get_and_set_director(director)
        cursor = self._db.cursor()
        cursor.execute( "INSERT INTO dvds "
                        "(title, year, duration, director_id) "
                        "VALUES (?, ?, ?, ?)",
                        (title, year, duration, director_id))
        self._db.commit()

    # -------------------------------------------------------------------------
    def get_and_set_director(self, director):
        director_id = self.get_director_id(director)
        if director_id is not None:
            return director_id
        cursor = self._db.cursor()
        cursor.execute("INSERT INTO directors (name) VALUES (?)", (director,))
        self._db.commit()
        return self.get_director_id(director)

    # -------------------------------------------------------------------------
    def get_director_id(self, director):
        cursor = self._db.cursor()
        cursor.execute( "SELECT id FROM directors WHERE name=?", (director,))
        fields = cursor.fetchone()
        return fields[0] if fields is not None else None

    # -------------------------------------------------------------------------
    def edit_dvd(self):
        title, identity = self.find_dvd("edit")
        if title is None:
            return
        title = Console.get_string("Title")
        if not title:
            return
        cursor = self._db.cursor()
        cursor.execute( "SELECT dvds.year, dvds.duration, directors.name "
                        "FROM dvds, directors "
                        "WHERE dvds.director_id = directors.id AND "
                        "      dvds.id=:id", dict(id=identity))
        year, duration, director = cursor.fetchone()
        director = Console.get_string("Director")
        if not director:
            return
        year = Console.get_integer("Year", "year", year, 1896, datetime.date.today().year)
        duration = Console.get_integer("Duration (minutes)", "minutes", duration, minimum=0, maximum=60*48)
        director_id = self.get_and_set_director(director)
        cursor.execute( "UPDATE dvds SET title=:title, year=:year, "
                        "duration=:duration, director_id=:director_id "
                        "WHERE id=:id", locals())
        self._db.commit()

    # -------------------------------------------------------------------------
    def find_dvd(self, message):
        """
        returns 2-tuple of title and id: (title, id)
        """
        message = "(Start of) title to " + message
        cursor = self._db.cursor()
        while True:
            start = Console.get_string(message, "title")
            if not start:
                return (None, None)
            cursor.execute( "SELECT title, id FROM dvds "
                            "WHERE title LIKE ? ORDER BY title",
                            (start + "%"))
            records = cursor.fetchall()
            if len(records) == 0:
                print("There are no dvds starting with", start)
                continue
            elif len(records) == 1:
                return records[0]
            elif len(records) > self.DISPLAY_LIMIT:
                print("Too many dvds ({0}) start with {1}; try entering more of the title".format(len(records), start))
                continue
            else:
                for i, record in enumerate(records):
                    print("{0}: {1}".format(i + 1, record[0]))
                which = Console.get_integer("Number (or 0 to cancel)", "number", minimum=1, maximum=len(records))
                return records[which - 1] if which != 0 else (None, None)

    # -------------------------------------------------------------------------
    def list_dvds(self):
        print(">>>>>>>>>>>>>>>>>>>>>> list_dvds")
        cursor = self._db.cursor()
        sql = ( "SELECT dvds.title, dvds.year, dvds.duration, directors.name FROM dvds, directors "
                "WHERE dvds.director_id = directors.id")
        start = None
        if self.dvd_count() > self.DISPLAY_LIMIT:
            start = Console.get_string("List those starting with [Enter=all]", "start")
            sql += " AND dvds.title LIKE ?"
        sql += " ORDER BY dvds.title"
        print()
        if start is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, (start + "%",))
        for record in cursor:
            print("{0[0]} ({0[1]}) {0[2]} minutes, by {0[3]}".format(record))


    # -------------------------------------------------------------------------
    def dvd_count(self):
        cursor = self._db.cursor()
        cursor.execute("SELECT COUNT(*) FROM dvds")
        return cursor.fetchone()[0]

    # -------------------------------------------------------------------------
    def remove_dvd(self):
        title, identity = self.find_dvd("remove")
        if title is None:
            return
        ans = Console.get_bool("Remove {0}?".format(title), "no")
        if ans:
            cursor = self._db.cursor()
            cursor.execute("DELETE FROM dvds WHERE id=?", (identity,))
            self._db.commit()

    def test(self, arg1, arg2, *arg3, **arg4):
        print("test - arg1=", arg1, " arg2=", arg2)

# -----------------------------------------------------------------------------
def get_parameters(func):
    args, varargs, varkw, defaults = inspect.getargspec(func)
    #print("varargs=", type(varargs), "  varkw=", type(varkw))
    returned_args = []
    for i, arg in enumerate(args):
        #print (i, "[arg=", arg, ":", type(arg), "]")
        if arg == "self":
            continue
        arg_val = Console.get_string( "".join(("arg-", str(i), " '", arg, "'")) )
        returned_args.append(arg_val)
    return returned_args

# -----------------------------------------------------------------------------
def main():
    myDbTest = MyDbTest()
    try:
        # file = sys.argv[1]

        # extract all routines from object in question into a list
        routines = []
        for routine in dir(myDbTest):
            class Dummy():
                def method(): pass
            method_type = Dummy().method.__class__
            if not routine.startswith("_"):
                selected_routine = getattr(myDbTest, routine)
                if selected_routine.__class__ != method_type:
                    continue
                routines.append(routine)

        selection = ""
        while True :
            try:
                print("\n")
                # prompt for selection from list of our routines
                for (i, routine) in enumerate(routines, start=1):
                    print(i, ": ", routine)
                selection = Console.get_string("Select ([q]uit)")
                if selection in ["q", "Q"]:
                    break
                try:
                    index = int(selection)
                    selected_str = routines[index-1]
                except (ValueError, IndexError):
                    continue

                # invoke the routine dynamically
                selected_routine = getattr(myDbTest, selected_str)
                args = get_parameters(selected_routine)

                logfunction(selected_routine)(*args)
            except KeyboardInterrupt:   # <ctrl+C>
                break

    except IndexError:
        print("\nUsage:\n\t", sys.argv[0], " <file>")

main()
